const connection = require('../../sql');

class RoleService {
	// 新增角色
	async createRole(params) {
		const { roleName, permissions, remark, createId } = params;
		const statement = `
      INSERT INTO sys_role (role_name, permissions, remark, create_id, create_time)
      VALUES (?, ?, ?, ?, ?)
    `;
		const values = [roleName, permissions || '', remark || '', createId, new Date()];
		const [result] = await connection.execute(statement, values);
		return { id: result.insertId };
	}

	// 获取角色列表（分页）
	async getRoleList({ roleName, page, limit }) {
		let statement = `
      SELECT 
        id, role_name AS roleName, permissions, remark, 
        del_flag AS delFlag, create_time AS createTime, update_time AS updateTime, 
        create_id AS createId, update_id AS updateId, is_lock AS isLock
      FROM sys_role
      WHERE del_flag = 'F'
    `;
		const params = [];
		if (roleName) {
			statement += ' AND role_name LIKE ?';
			params.push(`%${roleName}%`);
		}

		// 计算总数
		const countStatement = `SELECT COUNT(*) AS total FROM sys_role WHERE del_flag = 'F'${roleName ? ' AND role_name LIKE ?' : ''}`;
		const [countResult] = await connection.execute(countStatement, roleName ? [`%${roleName}%`] : []);
		// 分页
		statement += ' ORDER BY create_time ASC LIMIT ? OFFSET ?';
		params.push(String(limit), String((page - 1) * limit));

		const [rows] = await connection.execute(statement, params);
		return {
			page: Number(page),
			limit: Number(limit),
			totalPage: Math.ceil(countResult[0].total / limit),
			total: countResult[0].total,
			rows,
		};
	}
	// 编辑角色
	async updateRole(id, params) {
		const { roleName, permissions, remark, updateId } = params;
		const statement = `
      UPDATE sys_role
      SET role_name = ?, permissions = ?, remark = ?, update_id = ?, update_time = ?
      WHERE id = ? AND del_flag = 'F'
    `;
		const values = [roleName, permissions || '', remark || '', updateId, new Date(), id];
		const [result] = await connection.execute(statement, values);

		if (result.affectedRows === 0) {
			throw new Error('角色不存在或已被删除');
		}
		return result;
	}

	// 删除角色（支持批量）
	async deleteRole(ids, userId) {
		const placeholders = ids.map(() => '?').join(',');
		const statement = `
      UPDATE sys_role
      SET del_flag = 'T', update_id = ?, update_time = ?
      WHERE id IN (${placeholders}) AND del_flag = 'F'
    `;
		const params = [userId, new Date(), ...ids];
		const [result] = await connection.execute(statement, params);

		if (result.affectedRows === 0) {
			throw new Error('没有找到可删除的角色');
		}
		// 清理关联表（sys_user_role 和 sys_role_menu）
		await connection.execute(`DELETE FROM sys_user_role WHERE role_id IN (${placeholders})`, ids);
		await connection.execute(`DELETE FROM sys_role_menu WHERE role_id IN (${placeholders})`, ids);
		return result;
	}

	// 获取角色可分配的菜单列表和已选中菜单
	async getRoleMenus(roleId) {
		// 查询所有菜单（包括目录、菜单、按钮）
		const menuStatement = `
		  SELECT 
			id, pid, sort, title, menu_type_cd AS menuTypeCd, 
			permissions, name
		  FROM sys_menu
		  WHERE del_flag = 'F'
		  ORDER BY sort ASC, id ASC
		`;
		const [menus] = await connection.execute(menuStatement);

		// 查询该角色已关联的菜单 ID
		const roleMenuStatement = `
		  SELECT menu_id AS menuId
		  FROM sys_role_menu
		  WHERE role_id = ?
		`;
		const [roleMenus] = await connection.execute(roleMenuStatement, [roleId]);
		const selectIds = roleMenus.map((row) => row.menuId);

		// 构建菜单树
		const menuMap = new Map();
		menus.forEach((menu) => {
			menu.children = [];
			menu.deep = ''; // 按你的示例，所有 deep 设为空字符串
			menuMap.set(menu.id, menu);
		});

		const rootMenus = [];
		menus.forEach((menu) => {
			if (menu.pid === '0') {
				rootMenus.push(menu);
			} else {
				const parent = menuMap.get(menu.pid);
				if (parent) {
					parent.children.push(menu);
				}
			}
		});

		return {
			menuLists: rootMenus,
			selectIds,
		};
	}

	// 保存角色菜单关联
	async saveRoleMenus(roleId, menuIds) {
		// 开启事务
		const conn = await connection.getConnection();
		try {
			await conn.beginTransaction();

			// 删除该角色的现有菜单关联
			const deleteStatement = `
			DELETE FROM sys_role_menu
			WHERE role_id = ?
		  `;
			await conn.execute(deleteStatement, [roleId]);

			// 插入新的菜单关联
			if (menuIds && menuIds.length > 0) {
				const insertStatement = `
			  INSERT INTO sys_role_menu (role_id, menu_id)
			  VALUES ${menuIds.map(() => '(?, ?)').join(', ')}
			`;
				const params = menuIds.flatMap((menuId) => [roleId, menuId]);
				await conn.execute(insertStatement, params);
			}

			// 提交事务
			await conn.commit();
			return true;
		} catch (error) {
			// 回滚事务
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}
}

module.exports = new RoleService();
